from datetime import datetime
import pandas as pd
import pymysql
import time
from mysql.connector import cursor
# 指定Excel文件路径
excel_file = 'test.xlsx'

# 使用pandas读取Excel文件
df = pd.read_excel(excel_file)

# 假设你要处理的三列数据分别是'Column1', 'Column2', 'Column3'
# 确保这些列名与你的Excel文件中的列名匹配
columns = ['x', 'y', 'z']


# MySQL数据库连接
# 数据库配置
config = {
    'host': '192.168.3.104',
    'user': 'root',
    'password': 'root1234',
    'database': 'gwza_hardware',
    'charset': 'utf8mb4',
}
conn = pymysql.connect(**config)
cursor = conn.cursor()
memo = '{"label": "489017 511B", "pa": "100162.476562", "elect": "43"}'
anchors = '{}'


# 循环读取这三列的数据
for index, row in df.iterrows():
    # 获取三列的值
    x = row[columns[0]]
    y = row[columns[1]]
    z = row[columns[2]]
    update_time_new = datetime.now()

    # 处理你的数据
    # 例如，打印出来
    print(f'Row {index}:')
    print(f'Column1: {x}, Column2: {y}, Column3: {z}')
    print('---')
    cursor.execute(
        f"UPDATE map_device SET device_status=1,x={x}, y={y}, z={z}, update_time='{update_time_new}',anchors='{anchors}',memo='{memo}' WHERE device_id = 'GW511B'")
    conn.commit()
    time.sleep(0.1)

conn.close()


